﻿Imports System.Data
Imports System.Data.SqlClient

Public Class Alumno
    Public id As Guid
    Public nombre As String
    Public edad As Integer?
End Class


Module Module1
    Sub DeleteAlumno(ByVal alu As Alumno)
        Dim con As SqlConnection = Nothing
        Try
            con = New SqlConnection()
            Dim build As New SqlConnectionStringBuilder
            build.DataSource = "(local)"
            build.InitialCatalog = "cursodb"
            build.IntegratedSecurity = True
            build.Pooling = True
            build.MaxPoolSize = 5
            build.ConnectTimeout = 10
            con.ConnectionString = build.ConnectionString
            Dim cmd As New SqlCommand
            cmd.Connection = con
            cmd.CommandText = "usp_alumnos_delete"
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@id", alu.id)
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        Finally
            'seha que se produsca error o  no  (siempre)
            If (con IsNot Nothing) Then
                con.Close()

            End If
        End Try
    End Sub
    Sub UpdateAlumno(ByVal alu As Alumno)
        Dim con As SqlConnection = Nothing
        Try
            con = New SqlConnection()
            Dim build As New SqlConnectionStringBuilder
            build.DataSource = "(local)"
            build.InitialCatalog = "cursodb"
            build.IntegratedSecurity = True
            build.Pooling = True
            build.MaxPoolSize = 5
            build.ConnectTimeout = 10
            con.ConnectionString = build.ConnectionString
            Dim cmd As New SqlCommand
            cmd.Connection = con
            cmd.CommandText = "usp_alumnos_update"
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@id", alu.id)
            cmd.Parameters.AddWithValue("@edad", alu.edad)
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        Finally
            'seha que se produsca error o  no  (siempre)
            If (con IsNot Nothing) Then
                con.Close()

            End If
        End Try
    End Sub

    Sub InsertAlumno(ByVal alu As Alumno)
        Dim con As SqlConnection = Nothing
        Try
            con = New SqlConnection()
            Dim build As New SqlConnectionStringBuilder
            build.DataSource = "(local)"
            build.InitialCatalog = "cursodb"
            build.IntegratedSecurity = False
            build.UserID = "fxuser"
            build.Password = "Pa$$w0rd"
            build.Pooling = True
            build.MaxPoolSize = 5
            build.MinPoolSize = 3
            build.ConnectTimeout = 10

            con.ConnectionString = build.ConnectionString
            Dim cmd As New SqlCommand
            cmd.Connection = con
            cmd.CommandText = "usp_alumnos_insert"
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@id", alu.id)
            cmd.Parameters.AddWithValue("@nombre", alu.nombre)
            cmd.Parameters.AddWithValue("@edad", alu.edad)
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        Finally
            'seha que se produsca error o  no  (siempre)
            If (con IsNot Nothing) Then
                con.Close()

            End If
        End Try
    End Sub

    Function GetOneAlumno(ByVal criterio As String) As Alumno
        Dim result As Alumno = Nothing
        Dim con As SqlConnection = Nothing
        Dim reader As SqlDataReader = Nothing
        Try
            con = New SqlConnection()
            Dim build As New SqlConnectionStringBuilder
            build.DataSource = "(local)"
            build.InitialCatalog = "cursodb"
            build.IntegratedSecurity = False
            build.UserID = "fxuser"
            build.Password = "Pa$$w0rd"
            build.Pooling = True
            build.MaxPoolSize = 5
            build.ConnectTimeout = 10
            con.ConnectionString = build.ConnectionString
            Dim cmd As New SqlCommand
            cmd.Connection = con
            cmd.CommandText = "usp_alumnos_getone"
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@nombre", criterio)
            con.Open()
            reader = cmd.ExecuteReader(System.Data.CommandBehavior.SingleResult Or System.Data.CommandBehavior.SingleRow)
            Dim idindex = reader.GetOrdinal("id")
            Dim nombreindex = reader.GetOrdinal("nombre")
            Dim edadindex = reader.GetOrdinal("edad")
            If (reader.Read) Then
                result = New Alumno
                result.id = reader.GetGuid(idindex)
                result.nombre = reader.GetString(nombreindex)
                If (Not reader.IsDBNull(edadindex)) Then
                    result.edad = reader.GetInt32(edadindex)
                End If
            End If
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        Finally
            'seha que se produsca error o  no  (siempre)
            If (reader IsNot Nothing) Then
                reader.Close()
            End If
            If (con IsNot Nothing) Then
                con.Close()
            End If
        End Try
        Return result
    End Function

    Function GetAllAlumnos(Optional ByVal criterio As String = Nothing) As List(Of Alumno)
        Dim result As New List(Of Alumno)
        Dim con As SqlConnection = Nothing
        Dim reader As SqlDataReader = Nothing
        Try
            con = New SqlConnection()
            Dim build As New SqlConnectionStringBuilder
            build.DataSource = "(local)"
            build.InitialCatalog = "cursodb"
            build.IntegratedSecurity = True
            build.Pooling = True
            build.MaxPoolSize = 5
            build.ConnectTimeout = 10
            con.ConnectionString = build.ConnectionString
            Dim cmd As New SqlCommand
            cmd.Connection = con
            cmd.CommandText = "usp_alumnos_getall"
            cmd.CommandType = CommandType.StoredProcedure

            If (Not String.IsNullOrEmpty(criterio)) Then
                cmd.Parameters.AddWithValue("@nombre", criterio)
            End If

            con.Open()
            reader = cmd.ExecuteReader(System.Data.CommandBehavior.SingleResult)
            Dim idindex = reader.GetOrdinal("id")
            Dim nombreindex = reader.GetOrdinal("nombre")
            Dim edadindex = reader.GetOrdinal("edad")
            While (reader.Read)
                Dim alum = New Alumno
                alum.id = reader.GetGuid(idindex)
                alum.nombre = reader.GetString(nombreindex)
                If (Not reader.IsDBNull(edadindex)) Then
                    alum.edad = reader.GetInt32(edadindex)
                End If
                result.Add(alum)
            End While
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        Finally
            'seha que se produsca error o  no  (siempre)
            If (reader IsNot Nothing) Then
                reader.Close()
            End If
            If (con IsNot Nothing) Then
                con.Close()
            End If
        End Try
        Return result
    End Function
    Sub Metodo()
        Dim al As New Alumno
        al.id = Guid.NewGuid
        al.nombre = "test"
        al.edad = 4
        InsertAlumno(al)
    End Sub
    Sub Main()
        'For index = 1 To 20000
        '    Threading.ThreadPool.QueueUserWorkItem(AddressOf Metodo)
        'Next





        'Dim al As New Alumno
        'al.id = Guid.NewGuid
        'al.nombre = "test"
        'al.edad = 4
        'InsertAlumno(al)
        'Dim target = GetOneAlumno(al.nombre)
        'If (target IsNot Nothing) Then
        '    Console.WriteLine("exito insert ")
        'Else
        '    Console.WriteLine("fracaso")
        'End If

        'al.edad = 10

        'UpdateAlumno(al)
        'target = GetOneAlumno(al.nombre)
        'If (target IsNot Nothing And target.edad = 10) Then
        '    Console.WriteLine("exito update ")
        'Else
        '    Console.WriteLine("fracaso")
        'End If

        'DeleteAlumno(al)
        'target = GetOneAlumno(al.nombre)
        'If (target Is Nothing) Then
        '    Console.WriteLine("exito delete ")
        'Else
        '    Console.WriteLine("fracaso")
        'End If





        'Dim res = GetAllAlumnos("ia")
        'For Each al In res
        '    Console.WriteLine(al.id)
        '    Console.WriteLine(al.nombre)
        '    If (al.edad.HasValue) Then
        '        Console.WriteLine(al.edad)
        '    Else
        '        Console.WriteLine("edad no especificada")
        '    End If
        'Next
        Console.ReadLine()
    End Sub
End Module

'Clase
Public Class Cliente
    Public dni As String
    Public nombre As String

    Public Sub New()
        dni = "00000000"
        nombre = "Ingrese un nombre"
    End Sub

    Public Sub RealizarPago()
        Console.WriteLine("pagando " + nombre)
    End Sub
    Public orden As Ordenes
End Class
Public Class Ordenes

End Class





